﻿using System;
using System.Collections.Generic;
using System.Text;

using System.Data;
using System.Data.SqlClient;
using System.Configuration;
using System.Collections;
using System.Data.Common;

namespace DusonFramework.DataAccess.Utility
{
    using DusonFramework.DataAccess.Paging;

    /// <summary>
    /// SQLHelper
    /// </summary>
    public class SQLHelper
    {
        #region 私有变量

        private DbConnection _DBConnection;
        private DbCommand _DBCommand;
        private DbDataAdapter _DBDataAdapter;
        private DbTransaction _DBTrans;

        private PagingAbstract _pagingMethod;

        #endregion

        #region 构造函数
        public SQLHelper(DbDataAdapter da)
        {
            _DBConnection = da.SelectCommand.Connection;
            _DBCommand = da.SelectCommand;
            _DBDataAdapter = da;
        }

        public SQLHelper(DbConnection con, DbCommand com, DbDataAdapter da)
        {
            _DBConnection = con;
            _DBCommand = com;
            _DBDataAdapter = da;
        }
        #endregion

        #region 属性

        public DbConnection DBConnection
        {
            get { return _DBConnection; }

            set { _DBConnection = value; }
        }

        public DbCommand DBCommand
        {
            get
            {
                if (null != _DBCommand)
                {
                    _DBCommand.Connection = _DBConnection;
                }
                return _DBCommand;
            }

            set { _DBCommand = value; }
        }

        public DbDataAdapter DBDataAdapter
        {
            get
            {
                if (null != _DBDataAdapter)
                {
                    _DBDataAdapter.SelectCommand = _DBCommand;
                }
                return _DBDataAdapter;
            }

            set { _DBDataAdapter = value; }
        }

        /// <summary>
        /// 分页算法
        /// </summary>
        public PagingAbstract PagingMethod
        {
            get
            {
                return _pagingMethod;
            }

            set { _pagingMethod = value; }
        }
        #endregion

        #region 打开、关闭连接
        private void OpenConnection()
        {
            if (_DBConnection.State == ConnectionState.Closed)
            {
                _DBConnection.Open();
            }

            if (null != _DBTrans)
            {
                _DBTrans = null;
                _DBTrans.Dispose();
                _DBTrans = _DBConnection.BeginTransaction();
            }
        }

        private void CloseConnection()
        {
            if (null != _DBTrans)
            {
                _DBTrans.Dispose();
                _DBTrans = null;
            }

            if (null != _DBConnection && _DBConnection.State == ConnectionState.Open)
            {
                _DBCommand.Parameters.Clear();
                _DBConnection.Close();
            }
        }
        #endregion

        #region ExecuteNonQuery
        public int ExecuteNonQuery(string commandText)
        {
            return ExecuteNonQuery(CommandType.Text, commandText, null);
        }

        public int ExecuteNonQuery(string commandText, DbParameter[] parameter)
        {
            return ExecuteNonQuery(CommandType.Text, commandText, parameter);
        }

        public int ExecuteNonQuery(CommandType commandType, string commandText)
        {
            return ExecuteNonQuery(commandType, commandText, null);
        }

        public int ExecuteNonQuery(CommandType commandType, string commandText, DbParameter[] parameters)
        {
            CreateCommand(commandType, commandText, parameters);
            try
            {
                OpenConnection();
                int tmpValue = _DBCommand.ExecuteNonQuery();
                return tmpValue;
            }
            catch
            {
                return 0;
            }
            finally
            {
                CloseConnection();
            }
        }
        #endregion

        #region ExecuteDataSet
        public DataSet ExecuteDataset(string commandText)
        {
            return ExecuteDataset(CommandType.Text, commandText, null);
        }

        public DataSet ExecuteDataset(CommandType commandType, string commandText)
        {
            return ExecuteDataset(commandType, commandText, null);
        }

        public DataSet ExecuteDataset(string commandText, DbParameter[] parameter)
        {
            return ExecuteDataset(CommandType.Text, commandText, parameter);
        }

        public DataSet ExecuteDataset(CommandType commandType, string commandText, DbParameter[] parameters)
        {
            CreateCommand(commandType, commandText, parameters);

            DataSet ds = new DataSet();
            try
            {
                OpenConnection();
                _DBDataAdapter.Fill(ds);
                return ds;
            }
            catch
            {
                return null;
            }
            finally
            {
                CloseConnection();
            }
        }
        #endregion

        #region ExecuteReader
        public IDataReader ExecuteReader(string commandText)
        {
            return ExecuteReader(CommandType.Text, commandText, null);
        }

        public IDataReader ExecuteReader(CommandType commandType, string commandText)
        {
            return ExecuteReader(commandType, commandText, null);
        }

        public IDataReader ExecuteReader(string commandText, DbParameter[] parameter)
        {
            return ExecuteReader(CommandType.Text, commandText, parameter);
        }

        public IDataReader ExecuteReader(CommandType commandType, string commandText, DbParameter[] parameters)
        {
            OpenConnection();
            CreateCommand(commandType, commandText, parameters);
            try
            {
                IDataReader rdr = _DBCommand.ExecuteReader(CommandBehavior.CloseConnection);
                return rdr;
            }
            catch
            {
                return null;
            }
            finally
            {
                _DBCommand.Parameters.Clear();
                //CloseConnection(); 
            }
        }
        #endregion

        #region ExecuteScalar

        public object ExecuteScalar(string commandText)
        {
            return ExecuteScalar(CommandType.Text, commandText, null);
        }

        public object ExecuteScalar(CommandType commandType, string commandText)
        {
            return ExecuteScalar(commandType, commandText, null);
        }

        public object ExecuteScalar(string commandText, DbParameter[] parameter)
        {
            return ExecuteScalar(CommandType.Text, commandText, parameter);
        }

        public object ExecuteScalar(CommandType commandType, string commandText, DbParameter[] parameters)
        {
            try
            {
                OpenConnection();
                CreateCommand(commandType, commandText, parameters);
                object tmpValue = _DBCommand.ExecuteScalar();
                return tmpValue;
            }
            catch
            {
                return null;
            }
            finally
            {
                CloseConnection();
            }
        }
        #endregion

        #region Transaction

        private void BeginTrans()
        {
            if (null == _DBTrans)
            {
                _DBTrans = _DBConnection.BeginTransaction();
            }
        }

        public int ExecuteTrans(CommandType commandType, List<string> commandTextList)
        {
            try
            {
                OpenConnection();
                BeginTrans();

                foreach (string cmdTxt in commandTextList)
                {
                    _DBCommand.CommandText = cmdTxt;
                    _DBCommand.CommandType = commandType;
                    _DBCommand.Transaction = _DBTrans;
                    _DBCommand.ExecuteNonQuery();
                    //ExecuteNonQuery(commandType, cmdTxt);
                }

                _DBTrans.Commit();
                return 1;
            }
            catch
            {
                _DBTrans.Rollback();
                return 0;
            }
            finally
            {
                CloseConnection();
            }
        }

        /// <summary>
        /// 
        /// </summary>
        /// <param name="commandType"></param>
        /// <param name="procedures">SQL语句的哈希表（key为sql语句，value是该语句的SqlParameter[]）</param>
        public int ExecuteTrans(CommandType commandType, Hashtable htSQL)
        {
            try
            {
                OpenConnection();
                BeginTrans();

                foreach (DictionaryEntry de in htSQL)
                {
                    string cmdTxt = de.Key.ToString();
                    DbParameter[] cmdParms = (DbParameter[])de.Value;
                    ExecuteNonQuery(commandType, cmdTxt, cmdParms);
                }

                _DBTrans.Commit();
                return 1;
            }
            catch
            {
                _DBTrans.Rollback();
                return 0;
            }
            finally
            {
                CloseConnection();
            }
        }
        #endregion

        #region 私有函数
        private void CreateCommand(CommandType commandType, string commandText, DbParameter[] parameters)
        {
            _DBCommand.CommandText = commandText;
            _DBCommand.CommandType = commandType;

            if (null != _DBTrans)
            {
                _DBCommand.Transaction = _DBTrans;
            }

            if (null != parameters && parameters.Length > 0)
            {
                foreach (DbParameter parameter in parameters)
                {
                    if ((parameter.Direction == ParameterDirection.InputOutput || parameter.Direction == ParameterDirection.Input) &&
                        parameter.Value == null)
                    {
                        parameter.Value = DBNull.Value;
                    }

                    _DBCommand.Parameters.Add(parameter);
                }
            }
        }
        #endregion

        #region 分页
        /// <summary>
        /// 适用于Max、RowNumber
        /// </summary>
        /// <param name="table"></param>
        /// <param name="fields"></param>
        /// <param name="filter"></param>
        /// <param name="sortField"></param>
        /// <param name="sortType"></param>
        /// <param name="pageIndex"></param>
        /// <param name="pageSize"></param>
        /// <returns></returns>
        public string GetPagingSQL(string table, string fields, string filter, string sortField, int sortType, int pageIndex, int pageSize)
        {
            return PagingMethod.GetPagingSQL(table, fields, filter, sortField, sortType, pageIndex, pageSize);
        }

        /// <summary>
        /// 适用于TopTop、SetRowCount
        /// </summary>
        /// <param name="table"></param>
        /// <param name="primaryKey"></param>
        /// <param name="fields"></param>
        /// <param name="filter"></param>
        /// <param name="sortField"></param>
        /// <param name="pageIndex"></param>
        /// <param name="pageSize"></param>
        /// <returns></returns>
        public string GetPagingSQL(string table, string primaryKey, string fields, string filter, string sortField, int pageIndex, int pageSize)
        {
            return PagingMethod.GetPagingSQL(table, primaryKey, fields, filter, sortField, pageIndex, pageSize);
        }
        #endregion

    }
}
